﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetDocumentPath_V3]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetDocumentPath_V3];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetDocumentPath_V3]
    @docId int
AS
BEGIN
SET NOCOUNT ON;

SELECT
        s.*,
        (
            SELECT COUNT(1) 
            FROM dbo.UDS_Flow_Jump j
            WHERE j.Flow_Id = s.Step_Id AND j.Step_Id = s.Step_Id
        ) AS Jump_Count,
        d.Order_ID
    FROM 
        dbo.UDS_Flow_Step s,
        (
            SELECT DISTINCT Flow_ID, Step_ID, Order_ID
            FROM dbo.UDS_Flow_Path
            WHERE Doc_ID = @docId
        ) d
    WHERE
        d.Flow_ID = s.Flow_ID
        AND d.Step_ID = s.Step_ID
    ORDER BY
        d.Order_ID asc;

END
GO
